package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.condition.NewsNoticeCondition;
import com.fenrirtec.aepp.common.dao.NewsNoticeDao;
import com.fenrirtec.aepp.common.model.NewsNotice;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;

public class NewsNoticeDao_JDBCImpl implements NewsNoticeDao {
	
	private static final Logger logger = LoggerFactory.getLogger(NewsNoticeDao_JDBCImpl.class);

	@Override
	public List<NewsNotice> searchByCategory(Integer category) {
		if (logger.isInfoEnabled()) {
			logger.info("[NewsNotice_JDBCImpl#searchByCategory] start.");
		}

		PreparedStatement statement = null;
		try {
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  news_notice_id, \n")
				.append("  category, \n")
				.append("  title, \n")
				.append("  body, \n")
				.append("  valid_start_date, \n")
				.append("  valid_end_date, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag \n")
				.append("from \n")
				.append("  t_news_notice \n")
				.append("where \n")
				.append("  category = ? \n")
				.append("and \n")
				.append("  delete_flag = false \n")
				.append("order by \n")
				.append("  news_notice_id \n");
			if (logger.isTraceEnabled()) {
				logger.trace("[NewsNotice_JDBCImpl#searchByCategory] sql={}", sql);
			}
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, category);
			ResultSet resultSet = statement.executeQuery();			
			ResultSetMapper<NewsNotice> resultSetMapper = new ResultSetMapper<NewsNotice>();
			List<NewsNotice> newsNotices = resultSetMapper.mapResultSetToList(resultSet, NewsNotice.class);
			
			return newsNotices;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[NewsNotice_JDBCImpl#searchByCategory] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[NewsNotice_JDBCImpl#searchByCategory] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

	@Override
	public List<NewsNotice> searchByCnd(NewsNoticeCondition condition) {
		if (logger.isInfoEnabled()) {
			logger.info("[NewsNotice_JDBCImpl#searchByCnd] start.");
		}

		PreparedStatement statement = null;
		try {
			StringBuilder sql = new StringBuilder()
				.append("select \n")
				.append("  news_notice_id, \n")
                .append("  tender_id, \n")
                .append("  tender_title, \n")
				.append("  category, \n")
                .append("  category_name, \n")
				.append("  title, \n")
				.append("  body, \n")
				.append("  valid_start_date, \n")
				.append("  valid_end_date, \n")
				.append("  create_user, \n")
				.append("  create_date, \n")
				.append("  update_user, \n")
				.append("  update_date, \n")
				.append("  delete_flag \n")
				.append("from \n")
				.append("  v_news_notice \n")
				.append("where \n")				
				.append("  delete_flag = false \n");
			
			if (condition != null) {
                if (StringUtils.isNotEmpty(condition.getNewsNoticeId())) {
                    sql.append("and \n");
                    sql.append("  news_notice_id = ? \n");
                }
                if (condition.getTenderId() != null && condition.getTenderId().intValue() > 0) {
                    sql.append("and \n");
                    sql.append("  tender_id = ? \n");
                }
                if (condition.getCategory() != null && condition.getCategory().intValue() > 0) {
                    sql.append("and \n");
                    sql.append("  category = ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getTitle())) {
                    sql.append("and \n");
                    sql.append("  title like ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getBody())) {
                    sql.append("and \n");
                    sql.append("  body like ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidStartDateFrom())) {
                    sql.append("and \n");
                    sql.append("  valid_start_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidStartDateTo())) {
                    sql.append("and \n");
                    sql.append("  valid_start_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateFrom())) {
                    sql.append("and \n");
                    sql.append("  valid_end_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateTo())) {
                    sql.append("and \n");
                    sql.append("  valid_end_date <= ? \n");
                }
                
                if (condition.getTimeLimit() != null && condition.getTimeLimit().intValue() > 0) {
                    sql.append("and \n");
                    switch (condition.getTimeLimit().intValue()) {
                    case 1:
                        sql.append("  date_format(create_date, '%Y-%m-%d') >= date_format(date_add(date_sub(now(), interval 1 month), interval 1 day), '%Y-%m-%d') \n");
                        break;
                    case 2:
                        sql.append("  date_format(create_date, '%Y-%m-%d') >= date_format(date_add(date_sub(now(), interval 3 month), interval 1 day), '%Y-%m-%d') \n");
                        break;
                    case 3:
                        sql.append("  date_format(create_date, '%Y-%m-%d') >= date_format(date_add(date_sub(now(), interval 6 month), interval 1 day), '%Y-%m-%d') \n");
                        break;
                    case 4:
                        sql.append("  date_format(create_date, '%Y') = date_format(now(), '%Y') \n");
                        break;
                    case 5:
                        sql.append("  date_format(create_date, '%Y') < date_format(now(), '%Y') \n");
                        break;
                    default:
                        break;
                    }
                }
            }
            
            sql.append("order by \n");
            sql.append("  create_date desc \n");
            
            if (condition != null) {
                if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
                    sql.append("limit ?, ? \n");
                }
            }
            
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#searchByCnd] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            
            if (condition != null) {
                int index = 0;
                if (StringUtils.isNotEmpty(condition.getNewsNoticeId())) {
                    statement.setString(++index, condition.getNewsNoticeId());
                }
                if (condition.getTenderId() != null && condition.getTenderId().intValue() > 0) {
                    statement.setInt(++index, condition.getTenderId());
                }
                if (condition.getCategory() != null && condition.getCategory().intValue() > 0) {
                    statement.setInt(++index, condition.getCategory());
                }
                if (StringUtils.isNotEmpty(condition.getTitle())) {
                    statement.setString(++index, "%" + condition.getTitle() + "%");
                }
                if (StringUtils.isNotEmpty(condition.getBody())) {
                    statement.setString(++index, "%" + condition.getBody() + "%");
                }               
                if (StringUtils.isNotEmpty(condition.getValidStartDateFrom())) {
                    statement.setString(++index, condition.getValidStartDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getValidStartDateTo())) {
                    statement.setString(++index, condition.getValidStartDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateFrom())) {
                    statement.setString(++index, condition.getValidEndDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateTo())) {
                    statement.setString(++index, condition.getValidEndDateTo());
                }
                
                if (condition.getPage() != null && condition.getRows() != null && condition.getPage() > 0 && condition.getRows() > 0) {
                    statement.setInt(++index, (condition.getPage() - 1) * condition.getRows());
                    statement.setInt(++index, condition.getRows());
                }
            }
						
			ResultSet resultSet = statement.executeQuery();			
			ResultSetMapper<NewsNotice> resultSetMapper = new ResultSetMapper<NewsNotice>();
			List<NewsNotice> newsNoticeList = resultSetMapper.mapResultSetToList(resultSet, NewsNotice.class);
			
			return newsNoticeList;
			
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace("[NewsNotice_JDBCImpl#searchByCnd] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[NewsNotice_JDBCImpl#searchByCnd] finish.");
			}
			if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
		}
	}

    @Override
    public Integer countByCnd(NewsNoticeCondition condition) {
        if (logger.isInfoEnabled()) {
            logger.info("[NewsNotice_JDBCImpl#countByCnd] start.");
        }

        PreparedStatement statement = null;
        int count = 0;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("select \n")
                .append("  count(news_notice_id) as count \n")
                .append("from \n")
                .append("  v_news_notice \n")
                .append("where \n")             
                .append("  delete_flag = false \n");
            
            if (condition != null) {
                if (StringUtils.isNotEmpty(condition.getNewsNoticeId())) {
                    sql.append("and \n");
                    sql.append("  news_notice_id = ? \n");
                }
                if (condition.getTenderId() != null && condition.getTenderId().intValue() > 0) {
                    sql.append("and \n");
                    sql.append("  tender_id = ? \n");
                }
                if (condition.getCategory() != null && condition.getCategory().intValue() > 0) {
                    sql.append("and \n");
                    sql.append("  category = ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getTitle())) {
                    sql.append("and \n");
                    sql.append("  title like ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getBody())) {
                    sql.append("and \n");
                    sql.append("  body like ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidStartDateFrom())) {
                    sql.append("and \n");
                    sql.append("  valid_start_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidStartDateTo())) {
                    sql.append("and \n");
                    sql.append("  valid_start_date <= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateFrom())) {
                    sql.append("and \n");
                    sql.append("  valid_end_date >= ? \n");
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateTo())) {
                    sql.append("and \n");
                    sql.append("  valid_end_date <= ? \n");
                }
                
                if (condition.getTimeLimit() != null && condition.getTimeLimit().intValue() > 0) {
                    sql.append("and \n");
                    switch (condition.getTimeLimit().intValue()) {
                    case 1:
                        sql.append("  date_format(create_date, '%Y-%m-%d') >= date_format(date_add(date_sub(now(), interval 1 month), interval 1 day), '%Y-%m-%d') \n");
                        break;
                    case 2:
                        sql.append("  date_format(create_date, '%Y-%m-%d') >= date_format(date_add(date_sub(now(), interval 3 month), interval 1 day), '%Y-%m-%d') \n");
                        break;
                    case 3:
                        sql.append("  date_format(create_date, '%Y-%m-%d') >= date_format(date_add(date_sub(now(), interval 6 month), interval 1 day), '%Y-%m-%d') \n");
                        break;
                    case 4:
                        sql.append("  date_format(create_date, '%Y') = date_format(now(), '%Y') \n");
                        break;
                    case 5:
                        sql.append("  date_format(create_date, '%Y') < date_format(now(), '%Y') \n");
                        break;
                    default:
                        break;
                    }
                }
            }
            
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#countByCnd] sql={}", sql);
            }
            
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            
            if (condition != null) {
                int index = 0;
                if (StringUtils.isNotEmpty(condition.getNewsNoticeId())) {
                    statement.setString(++index, condition.getNewsNoticeId());
                }
                if (condition.getTenderId() != null && condition.getTenderId().intValue() > 0) {
                    statement.setInt(++index, condition.getTenderId());
                }
                if (condition.getCategory() != null && condition.getCategory().intValue() > 0) {
                    statement.setInt(++index, condition.getCategory());
                }
                if (StringUtils.isNotEmpty(condition.getTitle())) {
                    statement.setString(++index, "%" + condition.getTitle() + "%");
                }
                if (StringUtils.isNotEmpty(condition.getBody())) {
                    statement.setString(++index, "%" + condition.getBody() + "%");
                }               
                if (StringUtils.isNotEmpty(condition.getValidStartDateFrom())) {
                    statement.setString(++index, condition.getValidStartDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getValidStartDateTo())) {
                    statement.setString(++index, condition.getValidStartDateTo());
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateFrom())) {
                    statement.setString(++index, condition.getValidEndDateFrom());
                }
                if (StringUtils.isNotEmpty(condition.getValidEndDateTo())) {
                    statement.setString(++index, condition.getValidEndDateTo());
                }
            }
                        
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                count = resultSet.getInt("count");
            }
            
            return count;
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#countByCnd] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[NewsNotice_JDBCImpl#countByCnd] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }

    @Override
    public Boolean insert(NewsNotice newsNotice) {
        if (logger.isInfoEnabled()) {
            logger.info("[NewsNotice_JDBCImpl#insert] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("insert into t_news_notice ( \n")
                .append("  category, \n")
                .append("  tender_id, \n")
                .append("  title, \n")
                .append("  body, \n")
                .append("  valid_start_date, \n")
                .append("  valid_end_date, \n")
                .append("  create_user) \n")
                .append("values ( \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?, \n")
                .append("  ?) \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#insert] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, newsNotice.getCategory());
            if (newsNotice.getTenderId() != null && newsNotice.getTenderId().intValue() > 0) {
                statement.setInt(2, newsNotice.getTenderId());
            } else {
                statement.setNull(2, Types.NULL);
            }
            statement.setString(3, newsNotice.getTitle());
            statement.setString(4, newsNotice.getBody());
            statement.setString(5, newsNotice.getValidStartDate());
            statement.setString(6, newsNotice.getValidEndDate());
            statement.setString(7, newsNotice.getCreateUser());

            int count = statement.executeUpdate();         
            if (count == 1) {
                result = true;
            }
            return result;
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#insert] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[NewsNotice_JDBCImpl#insert] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }

    @Override
    public Boolean update(NewsNotice newsNotice) {
        if (logger.isInfoEnabled()) {
            logger.info("[NewsNotice_JDBCImpl#update] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("update \n")
                .append("  t_news_notice \n")
                .append("set \n")
                .append("  category = ?, \n")
                .append("  title = ?, \n")
                .append("  body = ?, \n")
                .append("  valid_start_date = ?, \n")
                .append("  valid_end_date = ?, \n")
                .append("  update_user = ?, \n")
                .append("  update_date = current_timestamp \n")
                .append("where \n")
                .append("  news_notice_id = ? \n")
                .append("and \n")
                .append("  delete_flag = false \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#update] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, newsNotice.getCategory());
            statement.setString(2, newsNotice.getTitle());
            statement.setString(3, newsNotice.getBody());
            statement.setString(4, newsNotice.getValidStartDate());
            statement.setString(5, newsNotice.getValidEndDate());
            statement.setString(6, newsNotice.getUpdateUser());
            statement.setInt(7, newsNotice.getNewsNoticeId());

            int count = statement.executeUpdate();         
            if (count == 1) {
                result = true;
            }
            return result;
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#update] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[NewsNotice_JDBCImpl#update] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }

    @Override
    public Boolean delete(Integer newsNoticeId) {
        if (logger.isInfoEnabled()) {
            logger.info("[NewsNotice_JDBCImpl#delete] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("delete from \n")
                .append("  t_news_notice \n")
                .append("where \n")
                .append("  news_notice_id = ? \n")
                .append("and \n")
                .append("  delete_flag = false \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#delete] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, newsNoticeId);

            int count = statement.executeUpdate();         
            if (count == 1) {
                result = true;
            }
            return result;
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#delete] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[NewsNotice_JDBCImpl#delete] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }

    @Override
    public NewsNotice findDetail(Integer newsNoticeId) {
        if (logger.isInfoEnabled()) {
            logger.info("[NewsNotice_JDBCImpl#findDetail] start.");
        }

        PreparedStatement statement = null;
        try {
            StringBuilder sql = new StringBuilder()
                .append("select \n")
                .append("  news_notice_id, \n")
                .append("  tender_id, \n")
                .append("  tender_title, \n")
                .append("  category, \n")
                .append("  category_name, \n")
                .append("  title, \n")
                .append("  body, \n")
                .append("  valid_start_date, \n")
                .append("  valid_end_date, \n")
                .append("  create_user, \n")
                .append("  create_date, \n")
                .append("  update_user, \n")
                .append("  update_date, \n")
                .append("  delete_flag \n")
                .append("from \n")
                .append("  v_news_notice \n")
                .append("where \n")
                .append("  news_notice_id = ? \n")
                .append("and \n")
                .append("  delete_flag = false \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#findDetail] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, newsNoticeId);
            ResultSet resultSet = statement.executeQuery();         
            ResultSetMapper<NewsNotice> resultSetMapper = new ResultSetMapper<NewsNotice>();
            NewsNotice newsNotice = resultSetMapper.mapResultSetToObject(resultSet, NewsNotice.class);
            
            return newsNotice;
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[NewsNotice_JDBCImpl#findDetail] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[NewsNotice_JDBCImpl#findDetail] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }

}
